package com.alex.statistics.controller;

import com.alex.statistics.pojo.Result;
import lombok.Getter;
import lombok.Setter;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.util.StringUtils;
import org.springframework.web.bind.annotation.*;

import javax.validation.executable.ValidateOnExecution;
import java.util.*;
import java.util.stream.Collectors;

@RestController
@RequestMapping("/dm")
@Configuration
public class DmTableController {

    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Value("${db.max-row}")
    private int maxColumnCount;
    // 最大允许的列数量
    private static final int MAX_COLUMNS_LENGTH = 10;

    /**
     * 获取达梦数据库SPTA模式下的所有表名称
     */
    @GetMapping("/tables")
    public Result<List<String>> getAllTableNames() {
        String sql = "SELECT TABLE_NAME FROM USER_TABLES ORDER BY TABLE_NAME";
        List<String> tables = jdbcTemplate.queryForList(sql, String.class);
        //查询所有视图
          sql = "SELECT VIEW_NAME FROM USER_VIEWS ORDER BY VIEW_NAME";
        List<String> views = jdbcTemplate.queryForList(sql, String.class);
        //合并返回
        tables.addAll(views);
        return Result.ok(tables);
    }

    /**
     * 接收表名和列名的JSON请求，查询指定数据
     * 请求格式: {"table": "table1", "columns": ["id", "name", "age"]}
     * columns为空则查询所有列，最大支持6个列
     */
    @PostMapping("/table-data")
    public Result<List<Map<String, Object>>> getTableData(@RequestBody TableColumnsRequest request) {
        // 验证请求参数
        if (request == null || !StringUtils.hasText(request.getTable())) {
            throw new IllegalArgumentException("表名不能为空");
        }

        String tableName = request.getTable().trim();
        List<String> columns = request.getColumns();

        // 处理列名列表（去重、过滤空值）
        List<String> columnList = new ArrayList<>();
        if (columns != null) {
            columnList = columns.stream()
                    .map(col -> col != null ? col.trim() : null)
                    .filter(StringUtils::hasText)
                    .distinct()
                    .collect(Collectors.toList());


        }

        // 执行查询
        return Result.ok(getTableDataByColumns(tableName, columnList));
    }

    /**
     * 实际执行查询的内部方法
     */
    private List<Map<String, Object>> getTableDataByColumns(String tableName, List<String> columns) {
        // 验证表名合法性
        if (!isValidTableName(tableName)) {
            throw new IllegalArgumentException("无效的表名: " + tableName);
        }

        // 验证表是否存在
        if (!isTableExists(tableName) && !isViewExists(tableName)) {
            throw new IllegalArgumentException("表或视图不存在: " + tableName);
        }

        // 处理列名
        String columnSqlPart;
        if (columns == null || columns.isEmpty()) {
            columnSqlPart = "*"; // 列为空则查询所有列
        } else {
            // 验证列名合法性
            Set<String> validColumns = new HashSet<>(getTableColumnsList(tableName));
            for (String column : columns) {
                if (!isValidColumnName(column) || !validColumns.contains(column.toUpperCase())) {
                    throw new IllegalArgumentException("无效的列名: " + column);
                }
            }
            // 拼接列名，使用双引号包裹以支持大小写敏感的列名
            columnSqlPart = columns.stream()
                    .map(col -> "\"" + col + "\"")
                    .collect(Collectors.joining(","));
        }

        // 执行查询
        String sql = "SELECT " + columnSqlPart + " FROM " + tableName + " FETCH FIRST " + maxColumnCount + " ROWS ONLY";
        return jdbcTemplate.queryForList(sql);
    }

    /**
     * 获取指定表的所有列名
     */
    @GetMapping("/table-columns/{tableName}")
    public Result<List<String>> getTableColumns(@PathVariable String tableName) {
        // 验证表名合法性
        if (!isValidTableName(tableName)) {
            throw new IllegalArgumentException("无效的表名");
        }

        // 验证表是否存在
        if (isTableExists(tableName)) {
            String sql = "SELECT COLUMN_NAME FROM USER_TAB_COLUMNS WHERE TABLE_NAME = ? ORDER BY COLUMN_ID";
            return Result.ok(jdbcTemplate.queryForList(sql, new Object[]{tableName.toUpperCase()}, String.class));

        }
        if (isViewExists(tableName)) {
            String sql = "SELECT COLUMN_NAME FROM USER_TAB_COLUMNS " +
                    "WHERE TABLE_NAME = ?   " +  // 关键：用TABLE_TYPE过滤视图
                    "ORDER BY COLUMN_ID";
            return Result.ok(jdbcTemplate.queryForList(sql, new Object[]{tableName.toUpperCase()}, String.class));
        }
        throw new IllegalArgumentException("表或视图不存在: " + tableName);

        // 查询表的所有列名

    }

    /**
     * 内部使用的获取列名列表方法
     */
    private List<String> getTableColumnsList(String tableName) {
        String sql = "SELECT COLUMN_NAME FROM USER_TAB_COLUMNS WHERE TABLE_NAME = ? ORDER BY COLUMN_ID";
        return jdbcTemplate.queryForList(sql, new Object[]{tableName.toUpperCase()}, String.class);
    }

    private List<String> getTableViewColumnsList(String tableName) {
        String sql = "SELECT COLUMN_NAME FROM USER_TAB_COLUMNS WHERE TABLE_VIEW = ? ORDER BY COLUMN_ID";
        return jdbcTemplate.queryForList(sql, new Object[]{tableName.toUpperCase()}, String.class);
    }

    /**
     * 验证表名合法性
     */
    private boolean isValidTableName(String tableName) {
        return tableName.matches("^[a-zA-Z0-9_]+$") && tableName.length() <= 30;
    }

    /**
     * 验证列名合法性
     */
    private boolean isValidColumnName(String columnName) {
        return columnName.matches("^[a-zA-Z0-9_]+$") && columnName.length() <= 30;
    }

    /**
     * 检查表是否存在
     */
    private boolean isTableExists(String tableName) {
        String checkSql = "SELECT COUNT(*) FROM USER_TABLES WHERE TABLE_NAME = ?";
        Integer count = jdbcTemplate.queryForObject(
                checkSql,
                new Object[]{tableName.toUpperCase()},
                Integer.class
        );
        return count != null && count > 0;
    }

    private boolean isViewExists(String tableName) {
        String checkSql = "SELECT COUNT(*) FROM USER_VIEWS WHERE VIEW_NAME = ?";
        Integer count = jdbcTemplate.queryForObject(
                checkSql,
                new Object[]{tableName.toUpperCase()},
                Integer.class
        );
        return count != null && count > 0;
    }

    /**
     * 请求参数实体类
     */
    @Getter
    @Setter
    public static class TableColumnsRequest {
        private String table;
        private List<String> columns;

    }
}
    